package com.ch.base.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.sql.Timestamp;
import java.util.ArrayList;
import java.util.List;

import com.ch.base.db.DBBean;
import com.ch.base.obj.QueryResultObj;
import com.ch.base.util.StringUtils;

public class SysSqlLogInfoDAO extends DBBean {
	public SysSqlLogInfoDAO(Connection con){
		super(con);
		this.setLogger(this.getClass().getName());
	}
	
	/**
	 * 新增 insert(Wrapper wrapper)：
	 * 	(1)	此method 回傳之型態為int，其值大於0表新增成功
	 * @param wra
	 * @return
	 * @throws Exception 
	 */
	public int insert(SysSqlLogInfoWrapper wra) throws Exception{
		int result = -1;
		StringBuffer sbSql = new StringBuffer();
		sbSql.append("insert into sys_sql_log_info ( "); 
		/*
		 *	以下請填入 excel 結果8 for select欄位用之資料
		 *	注意：(1)填入相關資料後，須把最後一列之 "," 改為 ")"
		 *		  (2)問號之數量須正確
		 *  範例： 
		 *  sbSql.append("       context_path, ");
		 *  sbSql.append("       request_uri, ");
		 *  sbSql.append("       server_name, ");
		 *  sbSql.append("       server_port, ");
		 *  sbSql.append("       servlet_path, ");
		 *  sbSql.append("       request_url, ");
		 *  sbSql.append("       table_name, ");
		 *  sbSql.append("       sql1, ");
		 *  sbSql.append("       sql2, ");
		 *  sbSql.append("       sql3, ");
		 *  sbSql.append("       login_ip, ");
		 *  sbSql.append("       login_dept_code, ");
		 *  sbSql.append("       login_user_id, ");
		 *  sbSql.append("       handle_dept_code, ");
		 *  sbSql.append("       handle_user_id, ");
		 *  sbSql.append("       update_type, ");
		 *  sbSql.append("       update_time, ");
		 */
		sbSql.append("       context_path, ");
		sbSql.append("       request_uri, ");
		sbSql.append("       server_name, ");
		sbSql.append("       server_port, ");
		sbSql.append("       servlet_path, ");
		sbSql.append("       request_url, ");
		sbSql.append("       table_name, ");
		sbSql.append("       sql1, ");
		sbSql.append("       sql2, ");
		sbSql.append("       sql3, ");
		sbSql.append("       login_ip, ");
		sbSql.append("       login_dept_code, ");
		sbSql.append("       login_user_id, ");
		sbSql.append("       login_user_card_type, ");
		sbSql.append("       handle_dept_code, ");
		sbSql.append("       handle_user_id, ");
		sbSql.append("       handle_user_card_type, ");
		sbSql.append("       update_type, ");
		sbSql.append("       update_time) ");

		
		sbSql.append("values (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)");
		
		int paramsCount = 0;
		Object[] params = new Object[19];
		/*
		 *	以下請填入 excel 結果3 for insert()用之資料
		 *	注意：(1)宣告Object[] params = new Object[17];時, 須注意陣列大小是否與問號之數量一致   
		 *  範例：
		 *	params[count++] = wra.getContextPath();
		 *	params[count++] = wra.getRequestUri();
		 *	params[count++] = wra.getServerName();
		 *	params[count++] = wra.getServerPort();
		 *	params[count++] = wra.getServletPath();
		 *	params[count++] = wra.getRequestUrl();
		 *	params[count++] = wra.getTableName();
		 *	params[count++] = wra.getSql1();
		 *	params[count++] = wra.getSql2();
		 *	params[count++] = wra.getSql3();
		 *	params[count++] = wra.getLoginIp();
		 *	params[count++] = wra.getLoginDeptCode();
		 *	params[count++] = wra.getLoginUserId();
		 *	params[count++] = wra.getHandleDeptCode();
		 *	params[count++] = wra.getHandleUserId();
		 *	params[count++] = wra.getUpdateType();
		 *	params[count++] = Timestamp.valueOf(wra.getUpdateTime());;
		 */
		params[paramsCount++] = wra.getContextPath();
		params[paramsCount++] = wra.getRequestUri();
		params[paramsCount++] = wra.getServerName();
		params[paramsCount++] = wra.getServerPort();
		params[paramsCount++] = wra.getServletPath();
		params[paramsCount++] = wra.getRequestUrl();
		params[paramsCount++] = wra.getTableName();
		params[paramsCount++] = wra.getSql1();
		params[paramsCount++] = wra.getSql2();
		params[paramsCount++] = wra.getSql3();
		params[paramsCount++] = wra.getLoginIp();
		params[paramsCount++] = wra.getLoginDeptCode();
		params[paramsCount++] = wra.getLoginUserId();
		params[paramsCount++] = wra.getLoginUserCardType();
		params[paramsCount++] = wra.getHandleDeptCode();
		params[paramsCount++] = wra.getHandleUserId();
		params[paramsCount++] = wra.getHandleUserCardType();
		params[paramsCount++] = wra.getUpdateType();
		params[paramsCount++] = Timestamp.valueOf(wra.getUpdateTime());
		
		try {
			PreparedStatement prep = this.getPreparedStatement(sbSql.toString());
			int count = 1;
			
			prep.setString(count++, wra.getContextPath());
			prep.setString(count++, wra.getRequestUri());
			prep.setString(count++, wra.getServerName());
			prep.setString(count++, wra.getServerPort());
			prep.setString(count++, wra.getServletPath());
			prep.setString(count++, wra.getRequestUrl());
			prep.setString(count++, wra.getTableName());
			prep.setString(count++, wra.getSql1());
			prep.setString(count++, wra.getSql2());
			prep.setString(count++, wra.getSql3());
			prep.setString(count++, wra.getLoginIp());
			prep.setString(count++, wra.getLoginDeptCode());
			prep.setString(count++, wra.getLoginUserId());
			prep.setString(count++, wra.getLoginUserCardType());
			prep.setString(count++, wra.getHandleDeptCode());
			prep.setString(count++, wra.getHandleUserId());
			prep.setString(count++, wra.getHandleUserCardType());
			prep.setString(count++, wra.getUpdateType());
			prep.setObject(count++, Timestamp.valueOf(wra.getUpdateTime()));
			
			log.debug(Thread.currentThread().getStackTrace()[1].getMethodName() + ": " + this.getPreparedSQL(sbSql.toString(), params));
			result = this.executeUpdate(prep);
			prep.close();
		} catch (SQLException e) {
			log.error(Thread.currentThread().getStackTrace()[1].getMethodName() + ": " + this.getPreparedSQL(sbSql.toString(), params));
			throw e;
		} 
	
//		int count = 0;
//		Object[] params = new Object[19];
//		/*
//		 *	以下請填入 excel 結果3 for insert()用之資料
//		 *	注意：(1)宣告Object[] params = new Object[17];時, 須注意陣列大小是否與問號之數量一致   
//		 *  範例：
//		 *	params[count++] = wra.getContextPath();
//		 *	params[count++] = wra.getRequestUri();
//		 *	params[count++] = wra.getServerName();
//		 *	params[count++] = wra.getServerPort();
//		 *	params[count++] = wra.getServletPath();
//		 *	params[count++] = wra.getRequestUrl();
//		 *	params[count++] = wra.getTableName();
//		 *	params[count++] = wra.getSql1();
//		 *	params[count++] = wra.getSql2();
//		 *	params[count++] = wra.getSql3();
//		 *	params[count++] = wra.getLoginIp();
//		 *	params[count++] = wra.getLoginDeptCode();
//		 *	params[count++] = wra.getLoginUserId();
//		 *	params[count++] = wra.getHandleDeptCode();
//		 *	params[count++] = wra.getHandleUserId();
//		 *	params[count++] = wra.getUpdateType();
//		 *	params[count++] = Timestamp.valueOf(wra.getUpdateTime());;
//		 */
//		params[count++] = wra.getContextPath();
//		params[count++] = wra.getRequestUri();
//		params[count++] = wra.getServerName();
//		params[count++] = wra.getServerPort();
//		params[count++] = wra.getServletPath();
//		params[count++] = wra.getRequestUrl();
//		params[count++] = wra.getTableName();
//		params[count++] = wra.getSql1();
//		params[count++] = wra.getSql2();
//		params[count++] = wra.getSql3();
//		params[count++] = wra.getLoginIp();
//		params[count++] = wra.getLoginDeptCode();
//		params[count++] = wra.getLoginUserId();
//		params[count++] = wra.getLoginUserCardType();
//		params[count++] = wra.getHandleDeptCode();
//		params[count++] = wra.getHandleUserId();
//		params[count++] = wra.getHandleUserCardType();
//		params[count++] = wra.getUpdateType();
//		params[count++] = Timestamp.valueOf(wra.getUpdateTime());
//
//		try {
//			log.info("insert(): " + this.getPreparedSQL(sbSql.toString(), params));
//			result = this.executeUpdate(sbSql.toString(), params);
//		} catch (SQLException e) {
//			log.error("SQL in " + this.getClass().getSimpleName() + " insert(): " + getPreparedSQL(sbSql.toString(), params) + " executed failed.");
//			throw e;
//		}
	
		return result;	
	}
	
	/**
	 * 查詢 query(Wrapper wrapper, String whereCondition, String other)：
	 * 	(1)	此method 回傳之型態為List<Wrapper>
	 * 	(2)	whereCondition 為不在 wrapper 傳入欄位的查詢條件，例如日期區間...等等，由呼叫者組好傳入，前面需含 " and " 做為啟始字串。
	 * 	(3)	other 字串可為 "order by..." 或 "group by..."，當字串不為 null 或 空字串時，取代整段預設 order by / group by 字串。
	 * 注意：(1)SysCodeMainWrapper須置換為所需之wrapper。
	 *       (2)whereCondition 只放 wrpper 沒法取的欄位
	 * @param wra
	 * @param whereCondition
	 * @param other
	 * @return
	 * @throws Exception
	 */
	public QueryResultObj query(SysSqlLogInfoWrapper wra, String whereCondition, String other) throws Exception{
		QueryResultObj queryResultObj = new QueryResultObj();
		List<SysSqlLogInfoWrapper> result = new ArrayList<SysSqlLogInfoWrapper>();
		StringBuffer sbSql = new StringBuffer();
		ArrayList al = new ArrayList();
		sbSql.append("select * "); 
		sbSql.append("  from sys_sql_log_info "); 
		sbSql.append(" where 1 = 1 ");
		
		/*
		 *	以下請填入 excel 結果2 for query()用之資料
		 *  範例：
		 *	if( wra.getGdsNo()!=null )   { sbSql.append(" and gds_no like ? "); al.add( wra.getGdsNo() ); }
		 *	if( wra.getMinCnt()!=null )   { sbSql.append(" and min_cnt like ? "); al.add( wra.getMinCnt() ); }
		 *	if( wra.getMaxCnt()!=null )   { sbSql.append(" and max_cnt like ? "); al.add( wra.getMaxCnt() ); }
		 *	if( wra.getUntCnt()!=null )   { sbSql.append(" and unt_cnt like ? "); al.add( wra.getUntCnt() ); }
		 *	if( wra.getUnt()!=null )   { sbSql.append(" and unt like ? "); al.add( wra.getUnt() ); }
		 *	if( wra.getOpn()!=null )   { sbSql.append(" and opn like ? "); al.add( wra.getOpn() ); }
		 *	if( wra.getNote()!=null )   { sbSql.append(" and note like ? "); al.add( wra.getNote() ); }
		 *	if( wra.getUpdateId()!=null )   { sbSql.append(" and update_id like ? "); al.add( wra.getUpdateId() ); }
		 *	if( wra.getUpdateType()!=null )   { sbSql.append(" and update_type like ? "); al.add( wra.getUpdateType() ); }
		 *	if( wra.getUpdateTime()!=null )   { sbSql.append(" and update_time like ? "); al.add( wra.getUpdateTime() ); }
		 */
		if( wra.getContextPath()!=null )   { sbSql.append(" and context_path like ? "); al.add( wra.getContextPath() ); }
		if( wra.getRequestUri()!=null )   { sbSql.append(" and request_uri like ? "); al.add( wra.getRequestUri() ); }
		if( wra.getServerName()!=null )   { sbSql.append(" and server_name like ? "); al.add( wra.getServerName() ); }
		if( wra.getServerPort()!=null )   { sbSql.append(" and server_port like ? "); al.add( wra.getServerPort() ); }
		if( wra.getServletPath()!=null )   { sbSql.append(" and servlet_path like ? "); al.add( wra.getServletPath() ); }
		if( wra.getRequestUrl()!=null )   { sbSql.append(" and request_url like ? "); al.add( wra.getRequestUrl() ); }
		if( wra.getTableName()!=null )   { sbSql.append(" and table_name like ? "); al.add( wra.getTableName() ); }
		if( wra.getSql1()!=null )   { sbSql.append(" and sql1 like ? "); al.add( wra.getSql1() ); }
		if( wra.getSql2()!=null )   { sbSql.append(" and sql2 like ? "); al.add( wra.getSql2() ); }
		if( wra.getSql3()!=null )   { sbSql.append(" and sql3 like ? "); al.add( wra.getSql3() ); }
		if( wra.getLoginIp()!=null )   { sbSql.append(" and login_ip like ? "); al.add( wra.getLoginIp() ); }
		if( wra.getLoginDeptCode()!=null )   { sbSql.append(" and login_dept_code like ? "); al.add( wra.getLoginDeptCode() ); }
		if( wra.getLoginUserId()!=null )   { sbSql.append(" and login_user_id like ? "); al.add( wra.getLoginUserId() ); }
		if( wra.getLoginUserCardType()!=null )   { sbSql.append(" and login_user_card_type like ? "); al.add( wra.getLoginUserCardType() ); }
		if( wra.getHandleDeptCode()!=null )   { sbSql.append(" and handle_dept_code like ? "); al.add( wra.getHandleDeptCode() ); }
		if( wra.getHandleUserId()!=null )   { sbSql.append(" and handle_user_id like ? "); al.add( wra.getHandleUserId() ); }
		if( wra.getHandleUserCardType()!=null )   { sbSql.append(" and handle_user_card_type like ? "); al.add( wra.getHandleUserCardType() ); }
		if( wra.getUpdateType()!=null )   { sbSql.append(" and update_type like ? "); al.add( wra.getUpdateType() ); }
		if( wra.getUpdateTime()!=null )   { sbSql.append(" and update_time like ? "); al.add( wra.getUpdateTime() ); }

		sbSql.append(whereCondition);
		
		if(StringUtils.isEmpty(other)){
			/*
				*	請填入預設之"order by..." 或 "group by..."
			 */
			sbSql.append(" order update_time desc ");
		}else{
			sbSql.append(other);
		}
		
		Object[] params = al.toArray();
	
		try {
			log.info(Thread.currentThread().getStackTrace()[1].getMethodName() + ": " + this.getPreparedSQL(sbSql.toString(), params));
			queryResultObj = this.executeQuery(SysSqlLogInfoWrapper.class, sbSql.toString(), params);			
		} catch (Exception e) {
			log.error(Thread.currentThread().getStackTrace()[1].getMethodName() + ": " + this.getPreparedSQL(sbSql.toString(), params));
			throw e;
		}
	
		return queryResultObj;	
	}
}
